PC-Blue - MS DOS Public Domain Library

home *** CD-ROM | disk | FTP | other *** search

/ PC-Blue - MS DOS Public Domain Library / PC-Blue MS-DOS Public Domain Library - NYACC.iso / vol248 / tutorial.asc < prev next >

Wrap

Text File | 1986-12-01 | 106.5 KB | 2,641 lines

A Brief Course in Advanced Lotus 1-2-3 Functions 1) Part I There are plenty of references where one can turn to learn the basics of spreadsheets, but very few teaches the advanced features of the various programs, and the user is often left to discover them by him (her) self. In this series of short courses, the less used, and often very useful functions of 1-2-3 will be discussed. These are functions that makes the difference between a ho-hum spreadsheet, and a great and fun tool to use. We will cover the macro capabilities, the creation of menus, and finally the word-processing function of 1-2-3, which are barely referred to, and yet can be quite convenient. The only knowledge that is assumed for this tutorial is that the reader is able to move in the worksheet, copy ranges, erase ranges, and other very basic procedure. Since it is easier to demonstrate the different function through examples, we will construct a hypothetical worksheet. For a change, this will not deal with the best way to invest your money, sell stocks and bond, or other financial applications. Instead we will use a medically flavored problem. As you know there has been a strong tendency in Europe, and in this country, to monitor the evolution of the pregnancy with ultrasound. This technique allow to see the baby in its mother's uterus and allows to measure how much it is growing. In practice the obstetrician refers his (her) patient to another physician that knows how to perform the examination. This later physician then sends a report to the obstetrician. This report should contain the name of the patient, the name of the referring physician, the measurements that were obtained, and the conclusions based on the findings. Although in real life, many more information are obtained, these will be sufficient to demonstrate the function that we want to learn. Let us start with a clean worksheet. If you are currently in another worksheet, save it with: /File Save (followed eventually by Replace) then type /Worksheet Erase If you start afresh, you will be in front of a clean worksheet. To start by an easy task, lets format the columns. The command to use is: /Worksheet Column Set 1 and you then answer to the prompt, by replacing the suggested value (9), by the values indicated below for each column: A = 1, B = 22, C = 4, D = 4, E = 6, F = 1, G = 5, H = 5, I = 5, J = 1, K = 5, L = 5, M = 5, N = 1, O = 2 You need to move to each column before setting its width. Now let us go in column B, row 1, and introduce the label: D A T A I N P U T A R E A which will define the data input area. To make it more fancy, we can go in column B row 2 (from now on I will refer to the cell address by its Lotus name: B2), and introduce the label: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ which makes a neat underline. You might have wanted to use \~ instead in B2. You would then have: /Copy from B2 to B2..D2 Then introduce the following label in the different cells: C3: ' Data C4: '# 1 D4: '# 2 E3: ^Mean G2: 'Predicted age G3: ' in week G4: '5th H4: '50th I4: '95th K2: 'Predicted size K3: ' in mm K4: '5th L4: '50th M4: '95th O5: '? 2 Your worksheet should look like this now: A B C D E F G H I J K L M NO 1 D A T A I N P U T A R E A 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size 3 Data Mean in week in mm 4 # 1 # 2 5th 50th 95th 5th 50th 95th 5 ? Let us now go in F6 and introduce a vertical bar: | and then /Copy from F6 to F6..F10 next we will do almost the same: /Copy from F6 to J6..J10 Note that in some cases it may indeed be easier to type cell addresses than to point. Pointing would have implied here that we would have to deanchor the range by pressing BackSpace, moved to the correct location (J6), reanchor the pointer, by pressing . and finally extend the range. Note that you do not need to press . twice when defining a range. Once is sufficient, but I find it disturbing to introduce one . and see two appear. Up to now we have only introduced kid'stuff. Be patient. We still have a few label to introduce so lets do that now: B6: " Biparietal Diameter (mm) B7: "Abdominal Diam #1 B8: "Abdominal Diam #2 B9: 'Abdominal Perimeter: B10: "Estimated Fetal Weight (g) --> B11: 'EFW (pounds + ounces) D11: 'lbs. F11: 'ounces A word of explanation about these labels. The "Biparietal Diameter" (BPD) is more or less the distance between the two ears of the baby. It is a well established measurement that is used among other thing to find the age of the baby, and it is used in the formula to derive the baby's weight. The abdominal diameters are two diameters measured at the level of the baby's liver, which are obtained (usually) perpendicular to each other. The abdominal diameters are mainly used in the estimation of the baby's weight. The abdominal perimeter (AP), as indicated by its name, is the perimeter of the baby's abdomen, and is obtained from the two abdominal diameters by a simple formula that we will see in a second. The estimated fetal weight (EFW) is an approximation of the weight of the baby. It is derived from the BPD and the AP, through a very complicated formula that we will see in a moment. Finally, since most american patients are not too familiar with the metric system, the gram in which the EFW is expressed are converted into pounds and ounces (rounded to the nearest ounce). 3 Your worksheet should look like this now: A B C D E F G H I J K L M NO 1 D A T A I N P U T A R E A 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size 3 Data Mean in week in mm 4 # 1 # 2 5th 50th 95th 5th 50th 95th 5 ? 6 Biparietal Diameter (mm) | | | 7 Abdominal Diam #1 | | | 8 Abdominal Diam #2 | | | 9 Abdominal Perimeter: | | | 10 Estimated Fetal Weight (g) --> | | | 11 EFW (pounds + ounces) lbs. ounces 12 Please note that we have prepared to take two measurements for each parameter: Data 1 and 2. All calculation will in fact be based on the mean of these to measurements as referred to in column E. 1-2-3 provides a "mean" function, which is @AVG(range). We could enter: E6: @AVG(C6..D6) Note that 1-2-3 is smart enough not to average data with empty cells. If our user only introduce one of the two data, the mean will still come out correct. We can refine this formula by rejecting values that are not physiologically possible, because they are too big for instance. The BPD should not be larger than 105 mm. To introduce this lets place a condition that the cell only displays the value if it below a certain limit. To do that, press EDIT (F2), then HOME, and introduce the appropriate changes as described below. When done press return. E6: @IF(@AVG(C6..D6)<105,@AVG(C6..D6),@ERR) which means: If the mean is below the maximum that we have set up, let the cell represent the mean (the second term in the formula), otherwise (the last term in the formula) let us display an error. This would do the trick nicely. It would be nice that the cells display something like "Please remeasure" but @if function in 1-2-3 can only deal with numbers, @NA, and @ERR. In certain conditions we would like, however, to avoid this unesthetic ERR message. We could trap it, and replace it by a nicer 0. There is a function called @ISERR that test if the cell contains an error or not. Its general formula is @ISERR(condition, response if condition true, response if condition is false) We can therefore modify our formula to read: E6: @IF(@ISERR(@IF(@AVG(C6..D6) <105,@AVG(C6..D6),@ERR)),0,@AVG(C6..D6)) Now you may want to play with the worksheet. First save it under the name BABY by typing: 4 /File Save BABY ~ The ~ sign refers to the <Return> key. You can now enter values in C6 and D6, and observe the reaction of the worksheet. When ready for more, let us save a lot of efforts, and do: /Copy from E6 to E7 Which introduces the same formula for the next line. We have a small modification to do: the upper limit for normal here in 120 mm and not 105. So press Edit (F2), then Home, then a couple of TAB and introduce the change. Now you can copy the correct formula into the next line: /Copy from E7 to E8 You should have: E6: @IF(@ISERR(@IF(@AVG(C6..D6) <105,@AVG(C6..D6),@ERR)),0,@AVG(C6..D6)) E7: @IF(@ISERR(@IF(@AVG(C7..D7) <120,@AVG(D7..C7),@ERR)),0,@AVG(C7..D7)) E8: @IF(@ISERR(@IF(@AVG(C8..D8) <120,@AVG(D8..C8),@ERR)),0,@AVG(C8..D8)) To be sure that the data are displayed with only one digit after the period, go to C6 and type /Range Format Fixed and enter 1 to replace the suggested 2 The cells E6 to E8 should now read: E6: (F1) @IF(@ISERR(@IF(@AVG(C6..D6) <105,@AVG(C6..D6),@ERR)),0,@AVG(C6..D6)) E7: (F1) @IF(@ISERR(@IF(@AVG(C7..D7) <120,@AVG(D7..C7),@ERR)),0,@AVG(C7..D7)) E8: (F1) @IF(@ISERR(@IF(@AVG(C8..D8) <120,@AVG(D8..C8),@ERR)),0,@AVG(C8..D8)) If you want to get rid of the values that you have introduced in the data area C6..D8, just type /Range Erase C6..D8 We can now introduce a simple formula. The formula for a circumference (C) is: C = Pi*D where Pi is the greek letter, and D the diameter of the circle. Let us assume that similarly, the abdominal perimeter (AP) is equal to the mean of the two abdominal diameters multiplied by PI (the greek letter). We thus would have: C9: (E7+E8)*@PI/2 This is fine, except that we could improve it slightly by using a value for @PI/2 since this is a constant that will never change. To do that, lets go into C9, and correct the formula to: C9: @PI/2 and then while still in C9, press <EDIT> then <CALC>. This will convert the formula into its value. This will save 1-2-3 one computation, each time the worksheet is recalculated. The value should appear as: 5 C9: 1.5707963268 We can now complete the formula by pressing <EDIT> again and completing the formula to read: C9: 1.5707963268*(E7+E8) We can now introduce the formula for the estimated fetal weight. This is unfortunately a long and complicated formula. This formula was developed by Sheppard et al. from Yale University. E10: 10^(-1.7492+0.166*(E6/10)+0.046*(C9/10)-2.646*((C9/10)*(E6/10))/1000))*1000 I know it looks terrible, but that's why having a computer is great: you introduce it once and you can use it forever ! Another thing is definitely puzzling. How come that the cell E10 displays the value: 17.81 when we have not introduced any value ? This is due to the fact that the equation starts with an independent term 1.7492, and that when everything else is equal to zero, the cell provide the value of: (10^(-1.7492))*1000. The formula as it is now is (almost) correct. We will see in a moment, that it still contains a small bug. It is however barely readable. These cell addresses are difficult to read. To change that, let us go in cell E6 and type: /Range Name Create and give the name BPD<Return><Return> The second <Return> serve to define that only cell E6 in the range. Similarly lets go in cell C9 and type: /Range Name Create and give the name ABD_PER<Return><Return> You might be tempted to have given a name such as AP. Try to avoid this practice. First it may be too short to be meaningful, and second, you might by error give it a number such as AP1, AP2... if you have many range referring to similar information. You would then be in deep trouble. Those name are in fact cell addresses, and 1-2-3 will use the value in the cell AP1, not in the range that you meant to give this name ! This is an important pitfall and be careful to remember it: it might otherwise be quite difficult to debug your formulas ! Now go in cell E10, and surprise the formula is now a lot more clear. It reads: E10:10^(-1.7492+0.166*(BPD/10)+0.046*(ABD_PER/10)-2.646 * ((ABD_PER/10)*(BPD/10))/1000))*1000 What about this pitfall that I mentioned earlier ? Go into cell C6, and 6 introduce a value. Immediately 1-2-3 answers by giving you a value for the EFW. This is incorrect, since the formula requires that you also provide the value for the abdominal diameters. You therefore have a wrong answer in your EFW, although nothing tells you about it in the worksheet. This is a very important point. The worksheet is totally stupid. It calculates whatever you want, but it is your responsibility to be sure that the calculations are based on the correct values, and that all values needed for the calculations are present. The solution is however quite simple. We have to introduce a formula that says, if any of the data is absent, do not calculate, and display 0. This is the formula: @IF(BPD=0#OR#E7=0#OR#E8=0,@FALSE,otherwise give the result of the formula) The formula would then become: E10:@IF(BPD=0#OR#E7=0#OR#E8=0,@FALSE, @ROUND((10^(-1.7492+0.166*(BPD/10) + 0.046*(ABD_PER/10)-2.646*((ABD_PER/10)*(BPD/10))/1000))*1000,0)) The @ROUND function is introduce to avoid value smaller than a gram: the formula is not that accurate, and it would give a false sense of precision. Let us give it a name by typing: /Range Name Create and give the name EFW<Return><Return> Our formula is now complete, and you can enjoy it by entering a few value in C6..D8. It is also a good time to save your worksheet if you have not taken the habit to do that frequently. By the way, when you work on a difficult worksheet, it will not be impossible that a worksheet that was originally correct, become all messed up at some point. If you only save it, and replace the old worksheet, chances are that soon or later you will have lost a previously correct formula. So it is a good habit to save successive version, such as Stage1, Stage2, Stage3 etc ... Another pearl: Suppose that you would like to have a copy of a complicated formula such as the one in E10, somewhere else in the worksheet. What would you do: /Copy it ? Well yes and no. If you /Copy it, and have not be careful to use absolute addresses (the $ sign in front of both the letter and the number portion of the cell address) you will end up will a complicated formula, that is totally garbelled up. If it is far from its original location, it might be a real pain to fix it. A much simpler solution is the following. Go into the cell that you want to copy. Press EDIT, than HOME, ' and then RETURN. What you have done it transform the formula into a very long label. You can now /Copy this label where ever you want. To make use of it, just do the reverse maneuver: press EDIT, HOME, then DEL, and then RETURN. Of course you have to do that in the original cell too. We have two small formula to include now. They will convert the EFW expressed in grams into its value in pounds and ounces: C11: @INT(EFW/453) 7 This find the largest integer of the EFW divided by the conversion factor between pounds and gram which is 453. Let us give C11 a name by typing: /Range Name Create and give the name LBWGT<Return><Return> We now have to calculate how many ounces there are. To do this we will take the difference between the EFW, and its larger integer in pounds. The rest is what should be converted in ounces. The number of ounces will be the differences divided by the conversion factor from grams to ounces, which is 28. And we also would like to round the value, to avoid false precision. The formula will then be: E11: @ROUND((EFW-LBWGT*453)/28,0) You have noticed that we haven't filled the formula for the columns on the right of F. This will come later: we first need some other parameters. Before saving the worksheet again, lets introduce two last labels: B13: 'C O M M E N T S: B14: '~~~~~~~~~~~~~~~~ Your worksheet should look like this now: A B C D E F G H I J K L M NO 1 D A T A I N P U T A R E A 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size 3 Data Mean in week in mm 4 # 1 # 2 5th 50th 95th 5th 50th 95th 5 ? 6 Biparietal Diameter (mm) 0.0 | | | 7 Abdominal Diam #1 0.0 | | | 8 Abdominal Diam #2 0.0 | | | 9 Abdominal Perimeter: 0 10 Estimated Fetal Weight (g) --> 0 | | | 11 EFW (pounds + ounces) 0 lbs. 0 ounces 12 13 C O M M E N T S: 14 ~~~~~~~~~~~~~~~~ 8 2) Part II In the previous part, we have dealt exclusively with the introduction of numbers. We now have to introduce the "administrative data", such as the name of the patient, physician, etc... The first part will be exceedingly simple: we will set the column width. See the first part if you are not too sure how to do it. Let us set the column width to: P = 25, Q = 3, R = 1, S = 3, T = 1, U = 4, V = 10, W = 10, X = 9 Then, just introduce the following series of labels: P1: "Patient's information: P2: "Last Name : P3: "First Name : P4: "Birthday (MM/DD/YY) : P6: "Referring physician: P9: "Operator: P12: "LMP (MM-DD-YY): P13: "Nbr of days elapsed: P14: "Nbr of weeks elapsed: P15: "Due Date (from LMP) : P17: 'Please move the cursor with the <-- and --> arrow P18: 'When you have seen the age, type an extra <RETURN> To make it more fancy go in P1 and type: /Range Unprotect <Return><Return> which will display the label in high intensity. Then do the same in P6, and P9. Then introduce a '/ in R4, T4, R12, T12. Do not forget the apostrophe, or else you initiate a command. Finally introduce the following labels: W1: '<-- Today's Date W4: '<-- Patient's age W13: 'days W14: 'weeks 9 Your worksheet should now look like this: P Q R S T U V W X 1 Patient's information: <-- Today's Date 2 Last Name : 3 First Name : 4 Birthday (MM/DD/YY) : / / <-- Patient's age 5 6 Referring physician: 7 8 9 Operator: 10 11 12 LMP (MM-DD-YY): / / 13 Nbr of days elapsed: days 14 Nbr of weeks elapsed: weeks 15 Due Date (from LMP) : 16 17 Please move the cursor with the <-- and --> arrow 18 When you have seen the age, type an extra <RETURN> This is fairly self explanatory, except maybe for the LMP. LMP is a common abbreviation that refers to the Last Menstrual Period. This is an important date since this is how the length of the gestation is counted. It is true that the baby is only conceived around 15 days after the first day of the LMP, but it is easier to count the gestation from the LMP, since most pregnant patients know the date of the LMP, and few the date of the conception. As you suspect, we will introduce a few formula in here too. You might expect that the first would be something like @TODAY in V1. Well not quite. If we were to do that, we would always have the current day in the worksheet, and if we were to reload in memory a worksheet save a few days or weeks earlier, the new date would erase the old date, and mess up all the calculations saved in the old worksheet. What we need, is more like a "stamping" of the date. We will see the trick used for that later. For now lets give the name DATE to the range V1. See in the earlier portion of this article if you are not too confident about how to do this. The next obvious formula that you want to introduce is the one that calculates the patient's age. But before doing that lets name the 3 ranges Q4, S4, U4, with the following names: Q4: MBIRTH S4: DBIRTH U4: YBIRTH The formula for the age of the patient is today's date - the birthday of the patient, divided by 365. Since the age on someone is usually expressed in years (for instance 34), without the decimal portion of the year (not as 34.68), we have to trim this with the @INT function. The formula then becomes: V4: @INT((@TODAY-@DATE(YBIRTH,MBIRTH,DBIRTH))/365) 10 In fact it would be more correct to use 365.25, instead of 365, to take into account the 29th of February that occurs every 4 years. But, again, this is irrelevant precision. As for the formula to obtain the EFW earlier, we have to make sure that all portion of the birthday are filled. If one was missing the calculation would be meaningless, and we would like the cell to display that the value is not available (@NA). We can therefore correct the formula to become: V4: @IF(YBIRTH=0#OR#MBIRTH=0#OR#DBIRTH=0,@NA, @INT((@TODAY-@DATE(YBIRTH,MBIRTH,DBIRTH))/365)) It will probably strike some of you that this formula still allows a potential pitfall. Think about it. What if you introduced as the year portion of the birthday as being 23 (short for 1923). What would happen ? Nothing ? Well that is the problem. The medical literature as never recorded a pregnancy in a 62 years old woman. So this formula does not account for this problem. Of course we could include a statement such as @IF age > than 45 years display ERR. That is to crude for our great program, and we will see how we can solve this later. Make a note (in your SideKick, or whatever else to check for this later. The next formula that we can introduce is the one in V12, that will find the Lotus 1-2-3 serial number of the days of the LMP. This one is a formula straight out of the box: V12: @DATE(U12,Q12,S12) Since the serial number type of display, is difficult to read, for most of us, lets transform it into something more understandable, by using the following command: /Range Format Date 1 <Return> And lets give it the name LMPDATE. By now you should be expert at giving names. From the value obtained in V12, we can compute how many days have elapsed since the beginning of the gestation. The formula is very simple, it is today's date - the date of the LMP. This is really of example where the Lotus date arithmetic is tremendous. The formula is: V13: +DATE-V12 However such a simple formula does not account for error in the introduction of the values. We can trap them in a simple way by checking that the number of days that have elapsed, is less than the maximal number of days of a normal gestation. Since a normal gestation is 40 weeks (280 days) and that the baby may be as much as 14 days late, if we assume 320 days we should be able to cover even the most rare case, and the patients whose ovulation are delayed. The number of days that have elapsed, should also be more than 0 of course. Thus the enhanced formula: V13: @IF((DATE-V12)>0#AND#(DATE-V12)<320,DATE-V12,@ERR) Another relation which is easy to obtain, and is used in many 11 calculations in the number of weeks that have elapsed, in other word, the age of the baby. This formula is straight forward: V14: +($V$13/7) Since we do not want all the number behind the period, we will get rid of them. The way we have gotten rid of unnecessary numbers previously was by taking the @INTeger of the number. We cannot do this here, since as mentioned earlier, this value is going to be used in some following calculations. What we in fact want is to keep the value as it is, but only show the integer of the number of weeks. This is done by formatting the cell /Range Format Fixed 0 Since this is the age of the baby, why not call this cell AGE, this will make further calculations a lot easier. /Range Name Create AGE~ We can also calculate when the baby should be expected. This is commonly called the Due Date. V15: (D1) @IF(@ISERR(LMPDATE),@ERR,@IF(@ISERR(V13),@ERR,LMPDATE+280)) You will have noticed that the cell will only display a result, when two conditions are filled: the LMPDATE is available, and is valid, as checked by the number of days elapsed (V13). Again you should fixe the cell attribute to display the date format #1, as you previously did in V12. Your screen should look like this now: P Q R S T U V W X 1 Patient's information: 01-Mar-85 <-- Today's Date 2 Last Name : 1 3 First Name : 4 Birthday (MM/DD/YY) : / / NA <-- Patient's age 5 6 Referring physician: 7 8 9 Operator: 10 11 12 LMP (MM-DD-YY): / / ERR 13 Nbr of days elapsed: ERR days 14 Nbr of weeks elapsed: ERR weeks 15 Due Date (from LMP) : ERR 16 17 Please move the cursor with the <-- and --> arrow 18 When you have seen the age, type and extra <RETURN> 12 This screen is now finished. It contains important information that we can now use to complete the right portion of the screen that we developed in the last session. As you remembered, we left the first screen to look like this: A B C D E F G H I J K L M NO 1 D A T A I N P U T A R E A 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size 3 Data Mean in week in mm 4 # 1 # 2 5th 50th 95th 5th 50th 95th 5 ? 6 Biparietal Diameter (mm) 0.0 | | | 7 Abdominal Diam #1 0.0 | | | 8 Abdominal Diam #2 0.0 | | | 9 Abdominal Perimeter: 0 10 Estimated Fetal Weight (g) --> 0 | | | 11 EFW (pounds + ounces) 0 lbs. 0 ounces 12 13 C O M M E N T S: 14 ~~~~~~~~~~~~~~~~ As we mentioned earlier, the BPD can be used to predict the age of the baby, and we should now be able to complete this. Besides, since we now know what is the age of the baby (from the LMP), we can predict what should be the size of the BPD, and what should be its weight. This is important, since when the user of our worksheet will introduce his numbers, it would be nice to tell him what size to expect, so that a grossly abnormal value can be remeasured, or verified. The predictions, however, are never absolute. There are always baby that are destined to be bigger or heavier than other. Therefore we should take that into account. You suspected that by the fact that we have introduced label for the 5th, 50th (mean) and 95th percentile confidence limits. What this means, is that there is 90% chances that the measurements obtained on the baby that is investigated will fall between the lower and higher confidence limits. Should it be falling outside, one might suspect that something is abnormal with the baby. The equation to predict the normal size of the BPD is: L6: -19.634+3.0209*AGE+0.042134*AGE^2-0.0011756*AGE^3 Here we can see the advantage of the use of name ranges. The formula appears a lot clearer, than if cell addresses had been used. If when you press return, 1-2-3 beeps at you, and you have checked that the formula is indeed correct, the problem is that you have forgotten to name the range, as suggested previously. It would be a pity to have to press ESC to go name the range: this would erase all the equation that you have entered, a pretty tedious job. If you know the cell address you could edit the formula by replacing AGE by the cell address. To show you another technique, O will not remind you of what the cell address was. The trick previously used to copy a formula at long distance without having to change all the cell addresses, can be used here again. While in the EDIT mode, press HOME, and add an apostrophe at the very beginning of the formula. As the upper right indicator on your screen mentions, by doing that you have transformed a formula into a long 13 label, that 1-2-3 can now accept. Now that you have saved your work, go and /Range Name the cell V14 and call it AGE. When you return to L6, press EDIT again, HOME, and delete the '. Now 1-2-3 will accept the formula. Remember this trick. It is very useful when you introduce long formula. Some formula can reach 240 characters. You won't be able to see them entirely in your screen: they will scroll right or left. Such long formula can be terrible to debug, and it is not always possible to split them in shorter pieces. In such cases the above trick allows you to get out of it, you can then use the /Print Printer Option Other Cell-Formula Quit Range ~ Go Quit to get a printed copy of the formula, whic is now a label. Often the problem will be unbalanced parenthesis, or incorrect use of the #AND#, #OR# kind of parameters. After this slight disgression, let us return to our formula in L6. As you have noticed, it appears that there is a bug: although no age has been introduced, it displays the value -19.634. Again this is the independent term of the polynomial equation. This demonstrates that polynomial equation should not be used outside their limit of definition. To trap this error lets edit the formula to make sure that nothing is displayed in the cell if the AGE is less then 12 weeks: L6: @IF(AGE<12,0,-19.634+3.0209*AGE+0.042134*AGE^2-0.0011756*AGE^3) We can now introduce the confidence limits. These are equal to the age, less (or plus) 1.66 standard deviations: K6: @IF(AGE<12,0,L6-(1.66*2.75373)) M6: @IF(AGE<12,0,L6+(1.66*2.75373)) Following exactly the same principle, we can also introduce the predicted age from the BPD in cell H6, as well as the confidence limits in cells G6 and I6: H6: (G) @IF(E6=0,0,2.810638+0.4665383*E6-0.003687003*E6^2+0.0000282043*E6^3) G6: (G) @IF(H6=0,0,H6-(1.66*1.08)) I6: (G) @IF(H6=0,0,H6+(1.66*1.08)) We can add one more "bell and whistle" by having the worksheet automatically flag for us if the value of the BPD is within the predicted size, or if it outside. The formula is: O6: @IF(E6=0,0,@IF(E6<K6#OR#E6>M6,1,0)) which would display a "1" if the BPD is outside the confidence limits, or a 0 if it is within. We can now introduce the equation to determine the predicted estimated fetal weigh. This is a difficult equation, it contain some exponentiation, and is almost a mile long: L10: 10^(0.5328769+0.1122225*AGE-0.000145731*AGE^2-0.0000202932*AGE^3) We have to make sure that the cell will not display wrong information by modifying it to look like: 14 L10: @IF(AGE<15,0,10^(0.5328769+0.1122225*AGE-0.000145731*AGE^2 -0.0000202932*AGE^3)) and the standard deviation would be: K10:@IF(L10=0#OR#AGE<15,0,L10-(10^(-2.72529+0.4042264*AGE- 0.01048152*AGE^2+0.000098495*AGE^3))) M10:@IF(L10=0#OR#AGE<15,0,L10+(10^(-2.573974+0.4008528*AGE- 0.01031992*AGE^2+0.0000961377*AGE^3))) Wheew, this was the last equation. By now no equations should ever threat you anymore ! Lets call the 5th percentile (K10) LOWEFW, and the 95th percentile (M10) HIGHEFW, using the /Range Name Command. We can now fill the last formula, that will check whether our baby is within or outside the normal weight predictions for its age: O10: @IF(EFW=0,0,@IF(EFW=0#OR#HIGHEFW=0,0,@IF(EFW<LOWEFW#OR#EFW>HIGHEFW,1,0))) You now have finished all the input screens, and your first screen should look like: A B C D E F G H I J K L M NO 1 D A T A I N P U T A R E A 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size 3 Data Mean in week in mm 4 # 1 # 2 5th 50th 95th 5th 50th 95th 5 ? 6 Biparietal Diameter (mm) 0.0 | 0 0 0 | ERR ERR ERR |0 7 Abdominal Diam #1 0.0 | | | 8 Abdominal Diam #2 0.0 | | | 9 Abdominal Perimeter: 0 10 Estimated Fetal Weight (g) --> 0 | | ERR ERR ERR |0 11 EFW (pounds + ounces) 0 lbs. 0 ounces 12 13 C O M M E N T S: 14 ~~~~~~~~~~~~~~~~ Well done. But that is not too terrifically exciting to move all over these two screens to introduce data. Isn't there any better system ? Of course there is, and this will be the third part. Do not forget to save your worksheet if you have not taken the habit to do it every 5 minutes or so. 15 3) Part III: Using Macros In the first two installments we have prepared a fairly complicated sets of data entry forms, in which most of Lotus 1-2-3 formula and error trapping have been used. To complete this quick teaching of the advanced features of 1- 2-3, will will now use an extensive series of macro to make the data entry easier, and even to use 1-2-3 for word processing ! Lets start by retrieving the worksheet. Press HOME, you should see the data entry form for the measurements. Press TAB once. You now have the patient's information entry form. Pressing TAB again bring you in an empty screen. For a change we will accept the default size of the column. Columns Y to AF should be visible now. Go in Z1 and type: Z1: 'MACROS Let us go in cell Y4 and enter the label: Y4: '\M and in Z4: '{GOTO}message~/XMmenu~ What does this last label means ? Well this will be our first macro. Note that it starts with an apostrophe. Although it is not always necessary to do so, I would strongly advise you to do so. A 1-2-3 macro is a series of instructions that can be invoqued by pressing a single combination of ALT and one letter. The {GOTO} is the way to place in the macro one of the special keys of the IBM PC keyboard. You simply type its name between the curly brackets. Your 1-2-3 manual has the list of all the keys that can be used in the macro, and the macro spelling of their name. Here we ask the program to go to a place called "message". This is simply a range that we will define that will contain a message. This is a handy technique. If you want to tell the user of your worksheet long stories about what they should do at what moment, you can include a series of message in the worksheet, and send the program the these location. Alternatively, you can /Range Erase the message range, and include its content within the macro. The macro will then type it for you. This last technique is slightly slower, but more flexible. Please note, and this is crucial, the ~ sign, just after message. This is the spelling of the key RETURN, in 1-2-3 language. Since you cannot include a Return within the macro, (this would terminate the entry) 1-2-3 has given a simple name to the Return key, which is by far the key that you will need the most. You have to include a ~ every time that you would have pressed returned if you were to do the series of command yourself. An easy trick is to perform the series of commands yourself noting VERY carefully what key you press. Be extremely careful about ~ and spacebar. These are the two major offenders, and 1-2-3 is absolutely not forgiving. Alternatively, if you are a Prokey User, you can store the macro in a Prokey key, exit from 1-2-3, write the new Prokey macro (prokey filename.pro/w), and then import it into your worksheet with the /File Import Text. You will have to change the key names, delete the <begdef> and <enddef>, and the *. This may look tedious, but it can save a lot of time for complicated macro. As you can see there is something more in the label we 16 introduce: /XMmenu~. This is a special macro instruction that tell 1-2-3 that a special command (/X) is about to come. The special command in this case is a Menu (M) called menu. This may appear terribly confusing ! In fact it is fairly simple. 1-2-3 allows you to make your own menus just like the one you see when pressing /. And that is what we are about to do. Another very important thing to know, is that a macro is just a label, until you give it a name. This name MUST be \ plus one letter, and only that (for one single exception that we will see later). The letter can be upper or lower case, it does not matter. Now it starts to make sense why we introduced this mysterious \M label in Y4. This is going to be the name of the macro that we have introduce in Z4. It is not mandatory to use this convention, but it is quite convenient to write down the name of the macro in the left adjacent cell. The cell that we will name, however, is the cell that contains the macro. Lets do it: /Range Name Create \M~~ There are two ~. The first terminates the name, the second, defines the range. You could cover more than one cell with the macro range for the name. This is, however, not recommended. If you move this range around, the range might be stretched, and complications are bounded to occur. Now go in Y5 and type: Y5:'Name This will be the first entry in our future menu. Since we said in the previous macro, that processing of the macro should continue (/X) in a menu (M) called "menu", it is logic that we now give the name "menu" to cell Y5. Again do a /Range Name Create Menu~~ We mentioned this "message" range, but we have not defined it yet. So press GOTO (F5) and answer P41. You will arrive to a virgin section of the worksheet. Type: /Range Name Create Message~~ then go into P42, and type: Please select an option from the above menu. Then press GOTO again, and answer Y1. If everything went smoothly, press <ALT M> and you should see a new menu in place of the familiar Worksheet ... menu. This one just says Name. Although this is great, it is not yet terribly exciting ! Press CTRL BREAK and GOTO Y1. There move to Z5, and enter: Z5: 'Data AA5: 'Comments AB5: 'Report AC5: 'Prepare AD5: 'Exit 17 These will be the future tasks that we want our worksheet to perform. Since the name are quite descriptive for you, you may not want to write anything else, but since we want anyone to be able to understand how our worksheet works, lets introduce some information: Y6: 'Enter administrative data for the patient Z6: 'Enter measurements AA6: 'Enter your comments and suggestions regarding this examination AB6: 'Print the report AC6: "Prepare for a entering a new patient WARNING: this erase the current data AD6: 'Quit the program, and return to LOTUS Access Menu Now press <ALT M> to see our progress. Isn't that great ? It looks just like the real thing doesn't it ? This show an important feature of the menu creation in 1-2-3. You have to give one name per option, one option in each adjacent cell (no more than 8), the total length of the option of the menu cannot exceed 64 characters (otherwise it would bump into the right upper corner command indicator), and there must be a blank cell to the right of the last option. Secondly, the second line must either contain a word of explanation, or be blank. Some of you probably had the curiosity to move in this menu. Note that as with the real 1-2-3 menu you can either point to, or press the first letter. If you want to give the first letter, no two option can start with the same letter. That why this macro contains the option "Prepare" instead of the more logical "Clear", which would have interfere with "Comment" or "New" that would have interfered with "Name". If two options start with the same letter, the first will always be selected if you select by letter. Pointing could work however. You now realize why when you want to /File Extract, you have to press /File Xtract, and not /File Extract, which would have collided with the /File Erase, also on the same menu. This has probably erase more files than any other peculiarity of 1-2-3. If you can devise a way to rename this Xtract command, call Mitch, he might want to included it in the next revision of 1-2-3. Those audacious among you that have selected an option in the menu, will have been disappointed: the only thing that happened was that the upper right status indicator changed from CMDREADY to READY. Not to impressive. By the way did you noticed this CMDREADY ? When you are executing a 1-2-3 macro, the letter CMD (for command) are placed in front of most of the usual commands (but not WAIT for instance). Why did nothing happened ? Why didn't we automatically start entering data ? Well we didn't told 1-2-3 what to do. This is the third important point to know about the menu in macro. We have seen what the two first line must contain, now we have discovered that in order for 1-2-3 to do something, we have to store in the next cell in the same column what should be done. This is very important. A macro, can extend other a very long series of cells, provided that they are contiguous, and on top of each other. Execution will stop as soon as 1-2-3 arrives at a blank cell. That makes sense in fact ! Also note that although we only named one cell (Y5) with the name, all cells to the right (only in cases of menu), and all cells below those are included in the same menu. Enough theory. Lets introduce those famous instructions that we want 1-2- 18 3 to do for us. And lets start by the entering the patient's name and related information. Y7: '{GOTO}p1~/XIpatname<>1~/XG\M~ This tells 1-2-3 to go to cell P1, and to execute a special macro command (/X) which is "Ipatname<>1~/XG\M~". I stands for If, and "patname' is a range. A command language without IF is not a command language. The way the /XI command works is the following. If the condition that is tested (in this case if "patname" is different from (<>) 1) is true, than proceed and execute what is on the same line (/XG\M~), otherwise, drop to the next line, and execute the commands on the next line. The /XG\M~ is a macro command (/X) that says to Go (G) to another cell, or range, or macro (\M) and execute what is inside. Note that you should not forget the ~ !!! Very important ! Now that we know what this is all about, what does it mean ? When we introduce the name of the patient, we do not want that by pressing Name (which will be the first choice of our menu) by error, to have to reenter the name again. So we need to see if the patient's name as already been entered. If it has, than we just return to the menu, no harm done. If it has not, than we should enter it. We would therefore like to be able to test if there is a name in the cell Q1 (go name it "patname", then come back). However, 1-2-3 does not let us do this. If we ask the value of a label, it will always answer 0, which is unfortunately the same answer that we would receive if the cell is empty. This does not allows us to make the test we need. The only solution is therefore, to place a value in the cell ahead of time, and we choose 1, in this case. Then test if the cell has a value different of one. If we introduce a label in it, the value 1 will be replaced by 0. This will allow us to perform the test, but I am sure that quite a few of you must think "This is totally, crazy, it is twice more work !". Well yes, and no. It is indeed another step, but you will see later that we can have 1-2-3 do the job for you. That is the second note you should write down for yourself, and see how we will solve it later. Note that by using the {GOTO}p1 instruction, we managed to have the label stored in P1 to show in the left upper corner of the screen, which makes it easier for the user to understand what he is doing. Finally, note the convention that upper cases are used to type 1-2-3 instructions or key names, while lower cases are used to type range name, cell addresses, and all the rest. Although this is not mandatory, it will make the reading of your macro a lot easier. For instance a macro such as: /PPOOUOCQRPOOR~GQ makes less sense than /PPOOUOCQRpoor~GQ. Now we can introduce the next commands: Y8: '/XLPatient's LAST NAME:~patname~ Y9: '/XLPatient's FIRST NAME:~patfname~ They introduce a new type of special macro commands: the /XL. This prompt the user to input a Label (L), and it stores it in a cell whose address follows the ~. The prompt can contain some information about what should be entered, in this cases: Patient's LAST NAME:, and Patient's FIRST NAME:, and store the information in the cells "patname" and "patfname". We have already named Q2 as patname, go and name Q3 as patfname. The /XL command accept 19 anything including numbers and blank responses (a simple return). This is important to remember. There is no way to make sure that a user will fill a cell that he MUST fill. The trick described earlier: preload the cell with a value, ask the question (/XL), then test the cell to see if the value is different from the value that you preloaded, will not work, since the user could type Return, to the question, therefore erasing the preloaded value, and fouling the test. This is probably one reason why innocent people bought Symphony, later to discover that it was so impossible to use ! The next command is very similar: Y10: '/XNMONTH of birthday (type 0 if unknown): ~mbirth~/XImbirth>12~/XGmonthbirth~ Since it is more than 78 characters it did not fit on a standard page, and it was therefore placed on two lines. Here we use the command /XN. This is the similar command to input a number, however, there is a major difference. The user cannot simply press Return to go to the next question. He must input a value. We therefore need to provide an escape mechanism in case our patient does not want to reveal her age. Inputing 0 will do. Since there is no 0 month, it should not introduce any confusion in the further calculation. If something else than 0 is introduced it will be stored in "mbirth". Again, please go and name Q4 with this name. Note that this line continues with a IF condition (/XI). What this IF condition does, is check that the value just stored in "mbirth" is larger than 12. This would refer to an impossible month, and should be trapped. If the value is indeed found to be too large, than processing goes to (/XG) a cell called "monthbirth" which as you guessed is Y10. This allows to make an infinite loop, that the user can only escape by given a valid answer, or a 0 to indicate that the answer is not available. Before forgetting it, please name Y10 "monthbirth". If the value that the user has introduced is valid, processing will continue at the next line which is: Y11: '/XImbirth=0~/XGphysintro~ Again we have an IF condition. Now we test to see if the value in mbirth is not 0. If it is, than there is no point in asking what is the day or year or birth of the patient, we should continue processing further down (/XG), where information regarding the referring physician ("physintro") are needed. As before if the tested condition is not true processing drops down on the next line: Y12: '/XNPatient's DAY of BIRTHDAY: ~dbirth~/XIdbirth>@VLOOKUP(mbirth,mthtable,1)~/XGdaybirth~ This is basically the same thing, except that to test if the day is possible we need a more sophisticated check-up. Indeed the day vary with which month is used. So we should test for the day and the month. This is easily done using the @VLOOKUP function, that you may have wonder how in the hell I would be able to introduce in this tutorial, without referring to some sort of interest rate kind of table. Well here it is: 20 Month table: mthtable 1 31 2 29 3 31 4 30 5 31 6 30 7 31 8 31 9 30 10 31 11 30 12 31 You should introduce this in Y19: 'Month table: mthtable Y20: 1 Y26: 7 Z20: 31 Z26: 31 Y21: 2 Y27: 8 Z21: 29 Z27: 31 Y22: 3 Y28: 9 Z22: 31 Z28: 30 Y23: 4 Y29: 10 Z23: 30 Z29: 31 Y24: 5 Y30: 11 Z24: 31 Z30: 30 Y25: 6 Y31: 12 Z25: 30 Z31: 31 The entry in [Y19: 'Month table: mthtable] is not indispensable, but it allows you to remember to name of the table (range). As you know there is no direct tricks in 1-2-3 to find the name of a range. When the range covers only one cell, than it is easy to /Copy the cell into a neighboring cell. Then by going into the neighboring cell, the control panel will tell you: +Cell_address, where Cell_address will either be letters and number such as AE23 for instance, or it will be the cell name. This trick does not work if the range is more than one cell large, and the only trick is to do: /Range Name Create, and point to the name that you want to investigate. It is easier, when using utility type of range as the one we just create to place its name just above. Now you should name the whole range Y20..Z31 with the name "mthtable". The formula that we have introduced in Y12 makes more sense now. The [/XIdbirth>@VLOOKUP(mbirth,mthtable,1)~/XGdaybirth~] means if the date of birth (dbirth) is bigger than the value found by the @VLOOKUP function in the month table (mthtable) for the month in question (mbirth), in the column which as an offset of 1, go to (/XG) daybirth to continue processing. Again, as you guessed daybirth is Y12, so please go name it accordingly. If the condition is not filled, that is if the day is within the correct limits, than processing continues on the next line. Otherwise the user is looped back, and asked the same question again. We should make the test a little more bullet proof by adding 21 Y12: '/XNPatient's DAY of BIRTHDAY: ~dbirth~/XIdbirth<1#AND#dbirth>@VLOOKUP(mbirth,mthtable,1)~/XGdaybirth~ which would trap the malicious user that would try to introduce a day 0, and fool our worksheet ! As you see 1-2-3 is incredibly versatile, and you can test most anything. Something, however, that you will not be able to test, is that the user introduce a value such as 23.46 day ! But such a malicious user should be fired anyway ! On to the next line. Y13: '/XNPatient's YEAR of BIRTHDAY: ~ybirth~/XI@year(@today)-ybirth>45~/XGyearbirth~ You remember this first note you had to remember about the patient age that should not be more than 45 years otherwise it would be unlikely that she gets pregnant ? Well now is the time to attack this problem. What the [/XI@year(@today)-ybirth>45~/XGyearbirth~] does is test that the year portion (@YEAR) of the current date (@TODAY), minus the year of the patients birthday (ybirth) is no greater than 45. If it is, than the program will loop back. Again please name Y13 with the name "yearbirth". We now have to complete a few more entries, and this should be kid's stuff for you by now ! Y14: '{CALC}{GOTO}p6~/XLPhysician's name:~physname~ Y15: '{GOTO}p9~/XLOperator:~operator~ You are already familiar with the {GOTO} trick. But what about this first {CALC} ? For whatever reason, when 1-2-3 in processing macro, it does not recalculates the worksheet. Since we would like the age of the patient to appear as soon as we have introduced her year of birth, we have to specifically ask 1-2-3 to recalculate the worksheet, thus the {CALC}. Please do not forget to name Y14 with the name "physname", and Y15 with the name "operator". Next comes our last challenge for this session: Y16: '{GOTO}p12~/RIlmp~{?} Y17: '/XG\m~ We have a new command (/RI) which means Range Input. We could have used a similar way to introduce the LMP of the patient, as we did to introduce her birthday. That would not have been very challenging however. Beside patients always know their birthday pretty well, but are not usually so sure about their last menstrual periods "It was September 16, no, I think it was the first of October." The entry system we used previously would not allow to loop back to change a value. Of course we could have made a small modification such as "Do you want to change ? Yes/No" and loop back, but this would have been outside the range of this tutorial. So lets try a different method. First go in cell P10 and /Range Name Create LMP~ and define the range P10..U15 22 Then go in Q12 and issue the command: /Range Unprotect Q12~ and do the same in cell S12 and U12. You will notice that these cells are now in high intensity. What we have done by using the /Range input is define a range in which one can introduce number. But since the cells are naturally "protected" (a strange concept, that I refer you to your manual to better understand) we need to /Range Unprotect some of them. If you run it now you will see the result. To do this go in cell Q2 and introduce the value 1, then press <ALT M>, and in the menu, select Name. This will allow you to debug any problem so far. The most likely problem would be and error such as Illegal cell or range name that would be displayed in the bottom left corner of the screen, with the incriminated cell or range on the second line on the top left of the screen. The likely error, is that you forgot to define one of the ranges, during one of the /Range Name Create operation. Fix the problem, then go in cell Q2 and introduce the value 1, then press <ALT M>, and in the menu, select Name to try again. When you reach the LMP input range you will note that you can only go in cells Q12, S12, and U12. What ever movements you use with the arrows cursor you will only be able to go in these cells, and most of the other movement keys will simply beep at you. To escape just introduce the values that you want, then press return. You may have to press return twice, if you are making an entry at the same time. The screen will then scroll and wait for you to press an extra return, before returning to the menu. This extra return is due to the {?} that we placed in Y16, and it allows you to contemplate your masterpiece before moving onto the next subject. Now that you have master all these concepts the rest of this part will just be a breeze ! We will now fill the rest of the menu option except for the Report option that will compose the next lesson. Go back to the menu in cell Y5, and introduce: Z7: '/RIdata~/XG\m~ This will introduce the data in the first screen. You will have to define a "data" range that extends from: /Range Name Create data and to the range limit question: A1..D8 Again the /XG\m~ is designed to bring the user back in the menu, when he (she) has finished entering data. The next option we have to complete is: AA7: '{GOTO}a13~/RIinpcmts~/XG\m~ Which is preparing the area for "free form" entry of comments that the user might want to include. Again we have to define a range whose name is inpcmts which is done by: /Range Name Create inpcmts and to the range limit question: A13..B20 23 The next option on the menu is the report. Since we will deal with this later, lets just temporarily introduce the following macro, that just loops back to the menu: AB7: '/XG\m~ The next option is also very simple. We could /Range Erase every field that we had previously used (patname, patfname, physname etc..) but besides being a lot of typing this would be much more time consuming than erasing big blocks of data at a time. So the following macro will clean our worksheet faster: AC7: '/REjunk1~/REjunk2~/REybirth~/REjunk4~ {GOTO}patname~1~/XG\m~ Again the series of command is longer than 80 characters so I had to cut it in two. BE VERY CAREFUL not to introduce any blank between the two part of the line when reconstituting it. You will also have noticed in the second line the [{GOTO}patname~1~] instruction. This is the answer to the second note that you had to remind yourself. We simply ask 1-2-3 to erase everything we do not need, than to go in the cell that will contain the patient's name, and introduce our test variable. So you see that although this is a few extra step, we do not have to care about them, 1-2-3 does them for us. The range that you have to define for Junk1 is Q2..Q12 Junk2 is S4..S12 Junk4 is C6..D8 Note that these are more easy to define than the previous one. Why no Junk3 range ? Well logically this would have been the range that would have erased the year of birth of the patient. Since this is only one cell and it already had a name, why not use it ? The final macro, to exit 1-2-3 is very simple: AD7: '/QYEY Note that if you have started from the Lotus Access Menu, you will be returned to it, and that there is no way to return directly to DOS, and continue in a batch file for instance. Something like /QYEYEY (for Exit Yes) will not work. If you want to go to DOS, then you have to start with 123.EXE, and not LOTUS.EXE. This may look a trivial matter, but you might have in your worksheet a menu that says something like: Do you want to go to WordStar dBase DOS or whatever. Under each of those choices you could have a macro that contains instructions such as: /PFnext~ROOUML0~QRwordstar~GQ/QY /PFnext~ROOUML0~QRdbase~GQ/QY 24 Which means: print to a file called NEXT, and replace the existing version by this one, using the option other unformated, with no left margin, a range called WordStar that could contain something like: cd\wp ws as you note these are DOS commands. That's why you have to print them with no left margin, and no header of course. Then the macro exits from 1-2-3, and you could get dumped into a previously running batch file that could say something like: cd\123 123 rename next.prn next.bat next and would therefore start the next application, without having to know anything about how you got there. Isn't that great ? It might be necessary to give at this point what the expected answers should be if you input some values. To do that press <ALT M>, select Name, and complete your screen to look like this: Patient's information: 01-Mar-85 <-- Today's Date Last Name : Foucher First Name : Adele Birthday (MM/DD/YY) : 5 / 4 / 56 28 <-- Patient's age Referring physician: Herodote Operator: Vesalius LMP (MM-DD-YY): 9 / 6 / 84 06-Sep-84 Nbr of days elapsed: 176 days Nbr of weeks elapsed: 25 weeks Due Date (from LMP) : 13-Jun-85 Please move the cursor with the <-- and --> arrow When you have seen the age, type and extra <RETURN> Since you won't be able to introduce the same date as I included, add the difference between the date that you enter and the sample date above, to the patient's birthday, and LMP. (We hope that Victor Hugo, will not resent the use of his wife in this tutorial, if you do not like it, you can include Sally Brown instead, but I did not wanted to get into copyright trouble with Schultz !). Then select Data and fill the next screen: 25 D A T A I N P U T A R E A ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicted age Predicted size Data Mean in week in mm # 1 # 2 5th 50th 95th 5th 50th 95th ? Biparietal Diameter 66 66.0 |23.8 25.6 27.4 | 60 64 69 |0 Abdominal Diam #1 55 55.0 | | | Abdominal Diam #2 53 53.0 | | | Abdominal Perimeter: 169 Estimated Fetal Weight (g) --> 677 | | 632 871 1193 |0 EFW (pounds + ounces) 1 lbs. 8 ounces C O M M E N T S: ~~~~~~~~~~~~~~~~ This should allow you to check that the equations that you have introduced produce the correct value. Although this may not be critical for a tutorial, a friend of yours might be pregnant, and you wouln'dt want to give her incorrect information should you demonstrate her this worksheet ! The last macro that we will introduce for this session is the dating macro: Z2: '{GOTO}message~/DFdate~@TODAY~~~/XG\M~ The /DFdate~@TODAY~~~ portion tells 1-2-3 to /Data Fill a range called "date", with the formula @TODAY, and accept the increment and maximal limit value. Then the macro initiate the menu system. The \0 is a particuliar macro, that is executed automatically when the program is started, but cannot be invoqued after that. It allows to store for you all sorts of chores that you would otherwise do manually, such as changing the default subdirectory etc... 26 4) Part IV: Word Processing with 1-2-3 In the previous three sections we have prepared a sophisticated series of instructions that allows us to enter data concerning our patient, and make a series of calculations based on these data. We are going to prepare in this installment all the necessary instructions to have 1-2-3 prepare a report out of these data. What one means with a report is apparently exceedingly variable. Most data-base programs have "report" facilities that are in fact sorted listing of subset of the original data. These typically look like columns of unattractive numbers. What we mean here by report is something that should attempt to duplicate what a secretary would send to the referring physician. We are (fortunately/unfortunately) not ready yet to be able to have 1-2-3 be as good as a real secretary, but as you will see, we can make noticeable effort in this direction. The first thing we will want the user to check before printing the report is that the printer is ON. Since the number of instructions that we will be using is large, and would otherwise mess up the clean menu we previously did, we will start a new menu such as: Is the printer "ON" ? Print the report A few important things here: As you see, 1-2-3 allows to branch menus to sub menu. This is extremely convenient, and allows to make fairly user- friendly applications. Also, note that the menu do not have to be spacially contiguous. We will see in a instant how to connect them. Lets us introduce the following instructions: AH1: 'Is the printer "ON" ? AH2: 'Please make sure that the printer is "ON", then select "Print the report" AI1: 'Print the report AI2: 'Please be patient, printing the report takes a few minutes !~ Which corresponds to the entry of the menu, and the previously described line of explanation. Also note that the menu options do not necessarily need to be a single word. A short sentence is perfectly valid, as long as the total of all the entries does do exceed the length of the screen, minus the command indicator. Note that here we place the "Is the printer "ON" " option first so that if the user press return without too much thought, he does not bypass the checking: we will simply make this entry loop back with the instruction on the third line of the menu being: AH3: '/XMAH1~ The user has therefore to make a conscientious effort ot select the next choice. This menu is unfortunately missing a crucial element. What if the user decides that he want to modify a value ? No way ! What we need to build in is a form of ESCAPE. As you know when you are using a real 1-2-3 menu you can always press ESC to back-up one step. If you press ESC in a menu that you create, 1-2-3 will simply dump you out of the menu. For a trained user that represent no problem, but to make the menu more user friendly we have to come with a better way than that. The easiest is by far to include an instruction such as: 27 AJ1: 'Main Menu AJ2: 'Returns to the main menu AJ3: '/XG\m~ that does nicely the job. When using such back looping option, try to forecast where the user might want to return. If you have a very complicated branching pattern, the user might not want to come all the way back to the root of the tree of menu, and you may have to include more than one option for back-looping. This system works perfectly, but it has a major disadvantage: each time you lose one of the possible options for your menu. A neater trick, but that does not work all the time is to introduce another instruction on the line, that does the looping. For instance if we modify AH3: '/XMAH1~ to become AH3: '/XMAH1~/XMmenu~ then by pressing ESC, 1-2-3 abandon the first instruction (/XMAH1~) and continues processing at the second (/XMmenu~). We have indeed achieved what we wanted: by pressing ESC, the user is backed up one level. The problem, is that this trick only works consistently within menu, and no as well with other instruction (/XL for instance). This is however one way to have your menu mimic very closely those of 1-2-3. To summarize, the general principle is: after the normal series of instructions, place a last instruction that returns the user to the previous level. Another problem, is that the user will not be able to press ESC twice to back up two levels in the menu. That would also drop him back in the worksheet. Nevertheless this is a good trick, that you should try to implement as much as you can. Here is another example on how to use it in the instruction for the printing option: AI3: '/REy35..ae100~/XGAH5~/XMAH1~ Here we erase the range Y35..AE100 which is the location in which we will prepare the report, then we go (/XG) to the rest of the printing instruction that are located in cell AH5. The program should never reach the last instruction (/XMAH1~), and will only arrive there if the user press ESC during execution of the beginning of the line. We are now ready to start the preparation of the report. To demonstrate that we can assemble the report out of little portion, we will start to assemble the second paragraph, that we will later integrate in the text. The first task that we will assign us is to print a paragraph that says what is the estimated gestational age, and when the baby is due. We know what is the estimated gestational age from the BPD. We calculated this value in H6. We also know when the baby is due. We have stored this value in V15 (in a range called: duedate). However, the estimated age is expressed in weeks and tenth of week. That is surely not a standard unit ! The tenth of week should therefore be converted in days. Besides the due date are expressed in the Lotus Date 1 format, which although is standard in Europe, (and I was told with the US Army), is not traditionally accepted in correspondence. We will want to change that too. The result we want to obtain is something like: 28 The estimated gestational age is 15 weeks and 3 days, based on the BPD. The expected due date based on the LMP is May 12, 1985. We could have the text included in the macro (we will see example of that later), but this is generally a less efficient solution. Instead, we will make a copy of it in a range that we will call "estage" (for estimate age) that will span B41..B45, so please /Range Name Create estage from B41..B45. We will complete the entry in each of those cells by including the following: B41: " The estimated gestational age is B42: @INT(H6) B43: 'weeks and B44: @INT((H6-B42)*7) B45: "days, based on the BPD. You recognize that @INT obtains the number of week of gestation, and transforms the decimal portion (the number minus its integer) in days by multiplying it by 7. We now would like to use the almost unused command called: /Range Justify and justify these five line into one normal sentence. If you try it, nothing will happen. Why ? Well 1-2-3 refuse to justify anything that contains a formula. No problem, we will calculate the values of the formulas, and transform them into numbers. Here we hit a first snag. If we calculate the formula, we will have no problems, except that the second time around, when we will want to use the formula, it will have disappeared, and be replaced by a number ! That's not too great, if we have to retype the formula each type, we might as well forget the whole worksheet !. What we can do to get around this, is copy the whole range "estage" into a temporary range that we could calculate. This is a nice and simple solution: the original formula are still there, and when we calculate the copied formula we do not mess the originals. But here again we hit a snag. Try for instance to /Copy estage~ to B50 By the way lets call B50 estage2 (so please /Range Name Create estage2 from B50). What happened ? Well you noticed it: both formula now refer to other cells than the one we wanted. What is the problem ? In the formula that we defined in B42 and B44, we use "relative reference" and not absolute reference, when we moved the formula the cell that are now referred to have no interest whatsoever in our problem. How to solve this last problem ? Easy transform the entries to absolute references by adding a $ sign in front of the letter and number coordinated of the cell address: B42: @INT($H$6) B44: @INT(($H$6-$B$42)*7) Another problem solved. So now lets try to /Range Justify the copied range. Ok, now we have copied our range, but the justification 29 still does not work on formula. So what we will do is transform these formulas into values. Fortunately this problem is relatively minor. This is easily done by going into the cell, pressing EDIT, then CALC. We now have a value. So now lets try again to /Range Justify What do we get ? Nothing ? Ha Ha, another problem: 1-2-3 not only refuses to justify text that includes formula, it also refuse to justify text that contains values. Fortunately this problem is relatively minor. What we need it to transform a value into a label. This is easily done by going into the cell, pressing EDIT, then HOME and adding an apostrophe before pressing return. This may sound terribly difficult, and it is indeed terribly tedious to do, but remember that now we are merely finding out what we will have to instruct 1-2- 3 to do. Once this is written into a macro, than it will be a breeze. We are now ready to write the first line of our macro. For reason of space I had to split it on two line. Please be careful not to include ANY blank space between the two segments of the line AH5: '{GOTO}estage2~/Cestage~~{DOWN}{EDIT}{CALC}{HOME}'{DOWN}{DOWN} {EDIT}{CALC}{HOME}'{END}{DOWN} You will see that virtually all book about 1-2-3 advise you to split long macros so that you can see the whole thing on one screen. That is a nice advice, but often quite impractical. The macro that we will develop her would be so long that it would in turn be impractical to read, besides it would produce a lengthy downward extension in an otherwise compact worksheet, which would dramatically increase the memory requirements. Finally when you include a IF condition (/XI) in a line, 1-2-3 expects the next line to correspond to the action to be taken when the condition is not fulfilled, and splitting line would therefore not work. The next line in our macro is: AH6: '{DOWN}The expected due date based on the LMP is {DOWN}@month(duedate)~{EDIT}{CALC}~ /RNDmonthdue~/RNCmonthdue~~/XCmonth?~{DOWN} which includes a fair number of new concepts. The @month is a formula that finds the month value out of a date (duedate). You are by now familiar with the {EDIT}{CALC}~ trick. Since we want to convert the number of the month into a word (June is this case) we have to invoke a subroutine to do that. Why not a @VLOOKUP formula ? Because 1-2-3 is not able to deal with alphanumeric character in such tables, and only numbers are accepted. @CHOOSE, another poorly documented formula, would not work for the same reasons. So will invoke a subroutine by using a subroutine call: /XR. The subroutine is essentially going to compare a value. If the comparison is false it will test the next value on the next line, otherwise it will enter in the cell the name of the month. Each line ends by a subroutine return (/XR~). MONTH? /XImonthdue=1~January~/XR~ /XImonthdue=2~February~/XR~ /XImonthdue=3~March~/XR~ 30 /XImonthdue=4~April~/XR~ /XImonthdue=5~May~/XR~ /XImonthdue=6~June~/XR~ /XImonthdue=7~July~/XR~ /XImonthdue=8~August~/XR~ /XImonthdue=9~September~/XR~ /XImonthdue=10~October~/XR~ /XImonthdue=11~November~/XR~ /XImonthdue=12~December~/XR~ Let us introduce this: AC18: 'MONTH? AC19: '/XImonthdue=1~January~/XR~ AC20: '/XImonthdue=2~February~/XR~ AC21: '/XImonthdue=3~March~/XR~ AC22: '/XImonthdue=4~April~/XR~ AC23: '/XImonthdue=5~May~/XR~ AC24: '/XImonthdue=6~June~/XR~ AC25: '/XImonthdue=7~July~/XR~ AC26: '/XImonthdue=8~August~/XR~ AC27: '/XImonthdue=9~September~/XR~ AC28: '/XImonthdue=10~October~/XR~ AC29: '/XImonthdue=11~November~/XR~ AC30: '/XImonthdue=12~December~/XR~ and give it the name: "month?". Note that "?", is a valid character in range name. We do not need any error trapping here since the value is derived from a date computed by 1-2-3. Note however that we will compare a range called "monthdue", and that we therefore need to name this range. It would be tempting to /Range Name cell B51 with the name "monthdue". That would not work however. In a few minutes we will /Range Justify this range. Now, when 1-2-3 Justifies a range, it loses not only its boundaries (which makes sense) but also it left upper corner and sometimes its name (which does not make sense). Therefore we have to have the macro create the name on the spot: thus the /RNCmonthdue~~ (note the TWO ~, one to accept the name, the second to accept the current cell). That would be quite simple, however, occasionally the range name might not have been destroyed, but simply stretched, often to include cell IV2048 ! It is therefore a good precaution to start by deleting a name that you want to create. This too has a small problem: the first time around, the range will not be there to be erased, and you therefore have to include a dummy range somewhere that you will call "monthdue". Please do that in AJ34 for instance. We have now found the month, and transformed it into a word that represent the name of the month. We now want to get the day and the year. The next line in the macro will be: AH7: '@day(duedate)~{EDIT}{CALC}{HOME}'{END}, 19{DOWN} @year(duedate)~{EDIT}{CALC}{HOME}'~ {UP}/RJ.{DOWN}~{EDIT}.{LEFT}{LEFT}{LEFT}{BS}~ The @day(duedate)~{EDIT}{CALC}{HOME}'{END}, 19{DOWN} portion is self 31 explanatory: @DAY extracts the value of the day out of monthdue. Note that to use a new formula we had to go to the next line. The {EDIT}{CALC}{HOME}' transforms the formula into a value, and then into a label to which {END}, 19{DOWN} adds a comma, a blank space and the value 19. The macro them goes in the next cell to get the year value of duedate @year(duedate)~ and then does the same kind of editing: {EDIT}{CALC}{HOME}'~. It is worth noticing that 1-2-3 does not return the century portion of the year, only the year itself, which is why we had to add it in the previous line. We now have the value of our date, but it looks terrible. B50: " The estimated gestational age is B51: '25 B52: 'weeks and B53: '4 B54: "days, based on the BPD. B55: 'The expected due date based on this gestational age is B56: 'June B57: '13, 19 B58: '85 A little creative justification will fix this in a second. {UP} moves the cursor one line up, on the day line, (/RJ) justifies the range extending from the anchored point (.) down {DOWN}~ one line. B55: 'The expected due date based on this gestational age is B56: 'June B57: '13, 19 85 We can now edit this cell {EDIT}, add a period at the end of the date (.), then move 3 to the left {LEFT}{LEFT}{LEFT} and remove {BS} the blank space between 19 and 85. B55: 'The expected due date based on the LMP is B56: 'June B57: '13, 1985. Our whole text is now there, and it is now a simple matter to rejustify it. To do so, introduce the next line: AH8: '{END}{UP}/RJ{END}{DOWN}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT} {RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}~/RNCestage2~.{END}{DOWN}~ again do not forget there should not be any blank between the two segment of line. We will start the justification at the upper left corner of the range. The fastest way to get there is to press {END}{UP} which will leave us at the interface between a filled and an empty cell. We then issue the /RJ command, and define the depth of the range we want to use by using END DOWN, then we define the lateral extend of the range with a series of {RIGHT}. The number of {RIGHT} is to compensate for the number of column that we have in the first screen. When you type those {RIGHT}, you will soon realize that Lotus could have abbreviated it into {->} or {R} ! If you have ProKey, this will be easier. Note that again, the /Range Justify, has delete the range name 32 "estage2", and that we have to recreate it, and define its extend. We have now prepared the first paragraph, and have covered most of the concept that we will need for the rest of the text. Let us go to the body of the letter and start creating it. AH9: "{GOTO}y41~To Dr{DOWN}/Cphysname~~{UP}/RJ{RIGHT}{RIGHT}{DOWN}~{RIGHT} {RIGHT}{RIGHT}{UP}{UP}{UP}{UP}{UP}{UP}" Examination performed on {RIGHT} {RIGHT}{RIGHT}@month(date)~/RNDmonthdue~/RNCmonthdue~~/XCmonth?~{DOWN} and in AH10: '@day(date)~{EDIT}{CALC}{HOME}'{END}, 19{DOWN}@year(date)~{EDIT} {CALC}{HOME}'~{UP}{UP}/RJ.{DOWN}{DOWN}{RIGHT}{RIGHT}~{EDIT}.{LEFT} {LEFT}{LEFT}{BS}~{DOWN}{DOWN} Note that for the first time I refer in a macro, to a cell address with it coordinate (Y41) and not through a range name. This is sometime useful when you are sure that something in your macro should never be moved around, will not enter in a formula... and especially when the cell will be part of a range that will be justified. This avoid the constant renaming of the cell. The rest of the line is fairly simple: we have 1-2-3 type the addressee: To Dr, then on the next line we copy the name of the addressee, justify the whole think to put it back on one line, then we move to the right and up. Again 1-2-3 introduce a label for us [" Examination performed on ] which is right justified by its prefix ("). We then reproduce (in cell AH10) our little date arithmetic that we previously used. This explains the reason to use a /XC to call a subroutine for the month. Otherwise we would have had to introduce this series of code twice. (Which would not have been a real big deal in fact: a /Copy would have worked quite nicely, since we were careful to use range name, and not cell addresses). We then introduce the next lines of instructions: AH11: '{LEFT}"Re:{RIGHT}/Cpatinfo~~{GOTO}ae39~/XIq4=0~ ~/XGreport1~ AH12: '+birthday~{EDIT}{CALC}{HOME}'{END} years old~ We again ask 1-2-3 to type something for us: ["Re:] and copy in the next cell the patient's information "patinfo". If you haven't defined this range yet do it by /RNC patinfo Q2..Q4. Here again we refer to a specific cell (AE39) which is easier, then using a range name. Then we test if Q4 is = to 0. If it is we introduce a blank (the blank between the two ~) which erase the display of the cell by concealing it behind a blank space. We then go to "report1" which is the name of the cell where execution should proceed. If Q4 is not equal to 0, then we copy onto this cell the value birthday (which is the age of the patient), transform it into a number, than a label, then go to the end of the label and add "years old". Lets introduce the next line: AH13: '{GOTO}Y41~{DOWN}{DOWN}{DOWN}{DOWN}Dear Dr{DOWN}/Cphysname~~{EDIT}, {UP}/RJ{RIGHT}{RIGHT}{RIGHT}{RIGHT}{DOWN}{DOWN}~ Nothing new here: we define a cell address, go to it, and do the same old 33 trick of copying a range, and reformating it. This cell should be named "report1" as you had guessed. AH14: '{DOWN}{DOWN}{DOWN}' Thank you for referring to us Ms{DOWN} /Cpatname~~{DOWN}/Cpatfname~~{EDIT} ~{DOWN} Here we have another little trick. Note the /Cpatfname~~{EDIT} ~{DOWN}. Since it is likely that sometime the user might be lazy, and not introduce the first name of the patient, we have to plan for such circumstance. If we were to leave a empty cell, when we will later use the END UP trick to define the range to justify, the pointer would stop at this level, since it will find an empty cell. This would thus be too early, and crash the macro. By introducing an additional blank space, we fill the cell with something invisible, but that 1-2-3 will recognize as a "filled" cell. The only inconvenient, is that the first name of the patient will be followed by two blank and not one, but we had admitted that we would not be as perfect as a real secretary ! AH15: 'who is currently{DOWN}/Cage~~{EDIT}{HOME}@INT~{EDIT}{CALC}{HOME}' {DOWN}weeks by dates. Here we have another little trick. When we copy age, we copy the formula: +($V$13/7). A minor bug of 1-2-3 if that if we /Copy it, we get the format of the cell together, and therefore not the numbers behind the period. Unfortunately, when we EDIT CALC it, they reappear. To get rid of them we need to use {EDIT}{HOME}@INT~ which gives us the integer of the formula. We did not used the integer previously because we wanted the full precision for our computations. The next cell does not poses any challenge anymore. We can now justify this paragraph by: AH16: '{UP}{UP}{END}{UP}/RJ.{END}{DOWN}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT} {RIGHT}{RIGHT}~ AH17: '{END}{DOWN}{DOWN}{DOWN} then copy the product of our earlier efforts: AH18: '/Cestage2~~{END}{DOWN}{DOWN}{DOWN} Note the {END}{DOWN}{DOWN}{DOWN} that is a way to find the end of the paragraph, then pass one line, and position the pointer to the next line to be ready to proceed with the next set of instructions. The next two lines offer few challenges: AH19: ' The estimated fetal weight (EFW) is{DOWN}/Cefw~~ AH20: '{EDIT}{CALC}{HOME}'{DOWN}grams (which is about{DOWN}/Clbwgt~~{EDIT} {CALC}{HOME}'{DOWN}pounds and {DOWN}/Cozwgt~~{EDIT}{CALC}{HOME}'{DOWN} ounces).{DOWN} we again have the now familiar trick of /Crange~~, remember the two ~, one to terminate the name of the range, the second to accept the current cell as the recipient. The copied value is then {EDIT}{CALC}{HOME}'{DOWN}. Note that it is not necessary to press return (~) after the apostrophe, pressing 34 {DOWN} is sufficient. You will notice later on that if you were to use the formula as they are, you would not obtain the expected value, although they are correct in the worksheet. What happened ? As in previous cases, by copying the formula you in fact change the cells that the formula refer to, and the formula loses in meaning. As before the solution is to transform the formulas in the original cell to indicate absolute references. So you should correct the formula on the next few lines to their new values on the second line. This merely involve adding plenty of $ signs ! change: E6: @IF(@ISERR(@IF(@AVG(C6..D6) <105,@AVG(C6..D6),@ERR)),0,@AVG(C6..D6)) into: E6: @IF(@ISERR(@IF(@AVG($C$6..$D$6) <105,@AVG($C$6..$D$6),@ERR)),0, @AVG($C$6..$D$6)) change: E10:@IF(BPD=0#OR#E7=0#OR#E8=0,@FALSE, @ROUND((10^(-1.7492+0.166*(BPD/10) + 0.046*(ABD_PER/10)-2.646*((ABD_PER/10)*(BPD/10))/1000))*1000,0)) into: E10:@IF($BPD=0#OR#$E$7=0#OR#$E$8=0,@FALSE, @ROUND((10^(-1.7492+0.166*($BPD/10) + 0.046*($ABD_PER/10)-2.646*(($ABD_PER/10)*($BPD/10))/1000))*1000,0)) change: C11: @INT(EFW/453) into: C11: @INT($EFW/453) change: E11: @ROUND((EFW-LBWGT*453)/28,0) into: E11: @ROUND(($EFW-$LBWGT*453)/28,0) change: V15: (D1) @IF(@ISERR(LMPDATE),@ERR,@IF(@ISERR(V13),@ERR,LMPDATE+280)) into: V15: (D1) @IF(@ISERR($LMPDATE),@ERR,@IF(@ISERR($V$13),@ERR,$LMPDATE+280)) change: L6: @IF(AGE=0,0,-19.634+3.0209*AGE+0.042134*AGE^2-0.0011756*AGE^3) into: L6: @IF($AGE=0,0,-19.634+3.0209*$AGE+0.042134*$AGE^2-0.0011756*$AGE^3) change: L10: @IF(AGE<15,0,10^(0.5328769+0.1122225*AGE-0.000145731*AGE^2 -0.0000202932*AGE^3)) into: L10: @IF($AGE<15,0,10^(0.5328769+0.1122225*$AGE-0.000145731*$AGE^2 -0.0000202932*$AGE^3)) change: K10:@IF(L10=0#OR#AGE<15,0,L10-(10^(-2.72529+0.4042264*AGE- 0.01048152*AGE^2+0.000098495*AGE^3))) 35 into: K10:@IF($L$10=0#OR#$AGE<15,0,$L$10-(10^(-2.72529+0.4042264*$AGE- 0.01048152*$AGE^2+0.000098495*$AGE^3))) change: M10:@IF(L10=0#OR#AGE<15,0,L10+(10^(-2.573974+0.4008528*AGE- 0.01031992*AGE^2+0.0000961377*AGE^3))) into: M10:@IF($L$10=0#OR#$AGE<15,0,$L$10+(10^(-2.573974+0.4008528*$AGE- 0.01031992*$AGE^2+0.0000961377*$AGE^3))) change: O10: @IF(EFW=0,0,@IF(EFW=0#OR#HIGHEFW=0,0,@IF(EFW<LOWEFW#OR#EFW>HIGHEFW,1,0))) into: O10: @IF($EFW=0,0,@IF($EFW=0#OR#$HIGHEFW=0,0, @IF($EFW<$LOWEFW#OR#$EFW>$HIGHEFW,1,0))) The next two lines will test whether the estimated fetal weight is normal or not: AH21: '/XIefw<lowefw~The estimated fetal weight indicated that the fetus is too small.~/XGreport2~ AH22: '/XIefw>highefw~The estimated fetal weight indicated that the fetus is too large. Exceptionally, there should be a blank space at the end of the first segment of the line when you reconstruct it. Note that the first line ends by [/XGreport2~]. This means that if the condition is true, the line of text is accepted, entered in the current cell, and that processing resume (/XG) at the cell called "report2". If the condition is false, than the next line (AH22) is executed. Here we do not need to send the processing to a special address after testing. Either the condition is true, and the line is accepted, and then processing follows on the next line, or the condition is false, the line is not accepted, and processing follows at the next line anyway. The next line will in fact justify the current paragraph, and is: AH23: '{UP}{END}{UP}/RJ{END}{DOWN}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT} {RIGHT}~ AH24: '{END}{DOWN}{DOWN}{DOWN} Again, you should /Range Name Create report2 for cell AH23. We can now copy the comments that have been introduced: AH25: '/Ccomment~~/RJ.{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{RIGHT}{RIGHT} {RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}~ The range "comment" is smaller than the range "inpcmts". Indeed, we do not want to copy the title COMMENT in our final report. Go name (/RNC) "comment" from B15..B20. 36 This last line (AH25), offers no difficulty, however, note that we did not use the previously described trick of using END DOWN to define the depth of the range. Why ? To use END DOWN to define the depth of the range, the range ought to be at least 2 lines deep. If it is only one line deep, than END DOWN will bring you to the end of the worksheet, not what you want to do of course. To avoid that pitfall we define ahead of time what we want to justify. We now the size of the incoming range, we defined it earlier. It might be tempting to justify a range that is the same size. But as you know, 1-2-3 allows to introduce long labels that are longer than a screen, and an advanced user that has notice that, might include in the range labels that are longer, and could not be justified within the same number of lines. We therefore have to exceed the area to be covered by the new range, to take this into account. After this justification, we are ready to move down to copy the closing remarks. We have up to now used the {END}{DOWN}{DOWN}{DOWN} sequence to do so. Since we do not know whether there will be a paragraph of comment or not, the way to do is to go many lines down first, then, find the last [empty cell - filled cell] interface and move then two lines down: AH26: '{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN} {DOWN}{END}{UP}{DOWN}{DOWN} This insures that with or without a paragraph of comment, the closing remarks will be correctly spaced. We can then copy the closing remarks: B33: ' Sincerely yours, This would be conveniently stored in B33, out of sight. Again you should name this range: /Range Name Create closing for cell B33 We are now ready to introduce the closing remarks in the letter: AH27: '/Cclosing~~{DOWN}{DOWN}{DOWN}{DOWN}{DOWN} and finish by introducing the operator, in the correct position for a signature: AH28: '{RIGHT}{RIGHT}{RIGHT}{RIGHT}/Coperator~~ Our letter is now finally finished. We only have to define the range that contains the letter, to be able to later print it. We could of course define a fixed range of 58 lines, starting just above the very first line that we used in the letter: Examination performed on "date". Although this would work, let us define a more sophisticated approach. The fixe range risks to crash if the user has introduced an unusual amount of comment, and has spilled over the 58 lines limits onto the next page. It would be embarrassing that our program, ignore such cases ! The way around it to start the range at an address we now is fix, Y35 for instance, then use something like END DOWN to extend the range. Unfortunately, as you know, all the text that we have introduced in the body of the letter, are just "long label" that are anchored in the left column, and are not "in" the adjacent cells. The cells on the right are indeed empty. The only cell that is not empty, is the one that contains the name of the operator. Ha Ha, it was not by chance that we placed it there ! We can in 37 fact start to define the range at Y35, anchor the pointer, move a few cell to the right, in fact exactly the correct amount to reach the same column in which the signature is stored, then plunge downward with a END DOWN command: AH29: '{GOTO}y35~/RNCletter~{BS}.{RIGHT}{RIGHT}{RIGHT}{RIGHT}{END}{DOWN} {RIGHT}{RIGHT}~ The {BS} is there to refuse the default range that 1-2-3 will suggest us when we give him the name of the range. This trick is nice, but there is a potential pitfall ! This trick rely crucially on the presence of something in the cell that contains the name of the operator ! If the user forgot (or neglect) to introduce it, that's it, the macro crashes. As you remember, the operator name was introduce by the instruction Y15: '{GOTO}p9~/XLOperator:~operator~ We are therefore sure that the user will have seen the message, but how about if he simply pressed return, no label would be stored in the cell. Now you remember from our discussion that there is no way to trap an empty message in a /XL command. Another trick that we previously used, preloading a cell with something - a blank space would be sufficient here -, would not work either, since by pressing return, the user would erase the blank space, and empty the content of the cell. The solution is then to add a blank after the signature has been copied. For that let us modify the previous line of instruction to read: change: AH28: '{RIGHT}{RIGHT}{RIGHT}{RIGHT}/Coperator~~ into: AH28: '{RIGHT}{RIGHT}{RIGHT}{RIGHT}/Coperator~~{EDIT} ~ Which place the blank space after the operator's name. If there is a name, it will go unnoticed, if there is nothing, the blank space will be sufficient to stop the END DOWN command. We have now solved all our problems and are ready to print our letter. Before that, I can't resist giving a last final trick for all of you out there, that print long report concerning First Trimester balance, Second Trimester balance, Third Trimester balance, and so on. You know that you can include in the header, and footer, all sorts of information, but not the value of a cell in the worksheet. This must have irritated quite a few people. 1-2-3 allows to include labels stored in the worksheet as title for graph labelling, but not in the header ! Well here is a simple way to do that. AH30: '/Cpatname~nametitle~ AH31: '/PPCAAOHPatient: AH32: ' AJ32: '!This line should remain! AH33: '~ML1~~R80~OFQ What this says is essentially: copy the value stored in a range in the worksheet into another range. This second range here will be "nametitle" which is in AH32. Nothing secret up to now. Then comes /PPCAAOHPatient: that 38 essentially means: Print to Printer after Clearing All previous printing conditions previously used, then Align the form, use Other conditions and include a Header that contains (left justified) the text:Patient. Now here come the trick. As you know 1-2-3 can read labels that follows each other on adjacent cells, as long as the next cell is situated in the cell just below. Even better you could even dream of splitting a command on to line. For instance {DO WN} would work perfectly as well as: {DOWN} Now no one with any sense would ever cut a single instruction on two levels like that, except that in our case, it fits perfectly what we need: the first portion remains constant, than we can introduce a variable that will be included in the header. AJ32: '!This line should remain! Simply reminds you not to compress the next line onto this one, which would result in erasing the next line of command, when the name is copied onto it. The rest, [~ML1~~R80~OFQ] starts by entering the name with a ~, then defines the correct margins, and quit the submenu to return to the main printing menu. Then: AH34: 'Rletter~GPQ/XG\m~ prints the letter (G), sends a page advance command (P), and returns to the menu that we designed earlier. In fact a very similar trick can be designed to automatically save the worksheet under a name stored in a cell from the worksheet. The macro should however check that the name follows the requirements of DOS filenames. I have included on the next page a sample of the type of printout that we can obtain. As you can see, it is fairly decent, and it is a pity that 1-2-3 word-processing abilities are so underutilized. It is of course tedious to prepare the printing macro, but once it is done it can be used multiple time, and that saves a lot of time. Besides, once the general principles involved are understood, we basically do the same things over and over. We have now toured most the advanced features of 1-2-3. You now deserve, to sit back, and enjoy the model that you have create, and admire the macros at work. I hope that this has tought you some tricks that will be useful in your own worksheets. Sincerely yours, Philippe Jeanty M.D.,Ph.D. 39 Patient: Foucher Examination performed on March 1, 1985. Re: Foucher Adele 28 years old To Dr Herodote Dear Dr Herodote, Thank you for referring to us Ms Foucher Adele who is currently 25 weeks by dates. The estimated gestational age is 25 weeks and 4 days, based on the BPD. The expected due date based on the LMP is June 13, 1985. The estimated fetal weight (EFW) is 677 grams (which is about 1 pounds and 8 ounces). This baby looks great, it size and weight are appropriate for its age and no evidence of congenital malformations are seen. The fetal sex is that of a female. Sincerely yours, Vesalius